import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.pool.DruidPooledConnection;
import dao.CartDao;
import dao.UserDao;
import entity.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.JdbcUtils;
import utils.JdbcUtilsV1;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;

public class Test {
//    public static void main(String[] args) {
//        System.out.println("Hello world!");
//        // jdbc
//        try {
//            // 获取连接
//            Connection connection = JdbcUtils.getConnection();
//            // 获取操作数据工具
//            PreparedStatement preparedStatement = connection.prepareStatement("select * from user");
//            // 执行sql
//            ResultSet resultSet = preparedStatement.executeQuery();
//            // 解析结果集
//            while (resultSet.next()) {
//                User user = new User();
//                user.setId(resultSet.getLong("id"));
//                user.setUsername(resultSet.getString("username"));
//                user.setPassword(resultSet.getString("password"));
//                System.out.println(user);
//            }
//            // 释放资源
//            JdbcUtils.close(connection, preparedStatement, resultSet);
//        } catch (Exception e) {
//            throw new RuntimeException(e);
//        }
//    }

    @org.junit.Test
    public void test1() throws SQLException {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/java");
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("rjxy");

        druidDataSource.setInitialSize(5);
        druidDataSource.setMinIdle(5);
        druidDataSource.setMaxActive(10);

        Connection connection = druidDataSource.getConnection();
        System.out.println(connection);
        // 使用这个连接 操作数据库

        connection.close();
    }

    @org.junit.Test
    public void test2() throws Exception {
        InputStream resourceAsStream = Test.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }

    @org.junit.Test
    public void test3() throws SQLException {
        Connection connection = JdbcUtilsV1.getConnection();
        System.out.println(connection);
        connection.close();
    }

    @org.junit.Test
    public void test4() throws SQLException {
        UserDao userDao = new UserDao();
        CartDao cartDao = new CartDao();

        Connection connection = JdbcUtilsV1.getConnection();
        connection.setAutoCommit(false);
        try {
            userDao.insert(connection);
            cartDao.insert(connection);
        } catch (Exception e) {
            // 回滚
            connection.rollback();
        } finally {
            connection.commit();
            connection.setAutoCommit(true);
            connection.close();
        }

    }

    @org.junit.Test
    public void test5() throws SQLException {
        // 添加
        // 获取连接
        Connection connection = JdbcUtilsV1.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        // sql
        String sql = "insert into user(username,password) values(?,?)";
        int update = queryRunner.update(connection, sql, "rjxy", "rjxy");
        System.out.println(update);
        JdbcUtilsV1.close();
    }

    @org.junit.Test
    public void test6() throws SQLException {
        // 删除
        // 获取连接
        Connection connection = JdbcUtilsV1.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        // sql
        String sql = "delete from user where username = ?";
        int update = queryRunner.update(connection, sql, "rjxy");
        System.out.println(update);
        JdbcUtilsV1.close();
    }

    @org.junit.Test
    public void test7() throws SQLException {
        // 修改
        // 获取连接
        Connection connection = JdbcUtilsV1.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        // sql
        String sql = "update user set password = ? where username = ?";
        int update = queryRunner.update(connection, sql, "6666", "lisi");
        System.out.println(update);
        JdbcUtilsV1.close();
    }

    @org.junit.Test
    public void test8() throws SQLException {
        // 查询
        Connection connection = JdbcUtilsV1.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        // 查询单行数据
        String sql = "SELECT * FROM user WHERE username = ?";
//        BeanHandler<User> userBeanHandler = new BeanHandler<>(User.class);
//        User admin = queryRunner.query(connection, sql, userBeanHandler, "admin");
//        System.out.println(admin);

        ResultSetHandler<User> userResultSetHandler = new ResultSetHandler<>() {
            @Override
            public User handle(ResultSet resultSet) throws SQLException {
                if (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String username = resultSet.getString("username");
                    String password = resultSet.getString("password");
                    User user = new User(id, username, password);
                    return user;
                }
                return null;
            }
        };
        User admin = queryRunner.query(connection, sql, userResultSetHandler, "admin");
        System.out.println(admin);

        // 查询多行数据
//        String sql = "SELECT * FROM user";
////        BeanListHandler<User> userBeanListHandler = new BeanListHandler<>(User.class);
//        List<User> users = queryRunner.query(connection, sql, new BeanListHandler<>(User.class));
//        System.out.println(users);

        // 查询单个值对象
//        String sql = "select count(*) from user ";
//        Long query = queryRunner.query(connection, sql, new ScalarHandler<Long>());
//        System.out.println(query);
        JdbcUtilsV1.close();
    }
}